package com.read.scriptures.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.read.scriptures.bean.CollectBean;
import com.read.scriptures.bean.HistoryBean;
import com.read.scriptures.model.Bookmark;
import com.read.scriptures.util.TimeUtils;

import java.util.ArrayList;
import java.util.List;

/**
 *
 */
public class HistoryDatabaseHelper extends SQLiteOpenHelper {

    private Context mContext;
    /**
     * 数据库名称
     */
    public static final String DB_NAME = "history.db";
    /**
     * 数据库版本
     */
    private static final int DB_VERSION = 5;

    public static final String TABLE_HISTORY = "history";
    public static final String TABLE_COLLECT = "collect";
    public static final String TABLE_BOOK_MARK = "book_mark";

    private SQLiteDatabase m_dbRead = null;
    private SQLiteDatabase m_dbWrite = null;

    public HistoryDatabaseHelper(Context context) {
        this(context, DB_NAME, DB_VERSION);
        mContext = context;
        m_dbRead = getReadableDatabase();
        m_dbWrite = getWritableDatabase();
    }

    public HistoryDatabaseHelper(Context context, String name) {
        this(context, name, DB_VERSION);
        mContext = context;
        m_dbRead = getReadableDatabase();
        m_dbWrite = getWritableDatabase();
    }

    public HistoryDatabaseHelper(Context context, String name, int version) {
        this(context, name, null, version);
        mContext = context;
        m_dbRead = getReadableDatabase();
        m_dbWrite = getWritableDatabase();
    }

    /**
     * 在SQLiteOpenHelper的子类当中，必须有该构造函数
     *
     * @param context 上下文对象
     * @param name    数据库名称
     * @param factory
     * @param version 当前数据库的版本，值必须是整数并且是递增的状态
     */
    public HistoryDatabaseHelper(Context context, String name, CursorFactory factory, int version) {
        // 必须通过super调用父类当中的构造函数
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createHistoryTable(db);
        createCollectTable(db);
        createRemarkTable(db);
        // execSQL用于执行SQL语句

    }


    private void createHistoryTable(SQLiteDatabase db) {
        // 该函数是在第一次创建的时候执行，实际上是第一次得到SQLiteDatabase对象的时候才会调用这个方法
        String sql = "create table " + TABLE_HISTORY + "(" + "id" + " Integer not null primary key autoincrement," + "volumeName" + " varchar(50)," + "volumeId" + " Integer," + "chapter" + " varchar(50)," + "chapterCount" + " varchar(50)," + "indexId" + " Integer,"
                + "content" + " varchar(50)," + "categoryId" + " Integer," + "parentId" + " Integer," + "parentPath" + " varchar(50)," + "time" + " varchar(50)," + "topIndex" + " Integer)";
        db.beginTransaction();
        try {
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    private void createCollectTable(SQLiteDatabase db) {
        // 该函数是在第一次创建的时候执行，实际上是第一次得到SQLiteDatabase对象的时候才会调用这个方法
        String sql = "create table " + TABLE_COLLECT + "(" + "id" + " Integer not null primary key autoincrement," + "volumeName" + " varchar(50)," + "volumeId" + " Integer," + "chapter" + " varchar(50)," + "chapterCount" + " varchar(50)," + "indexId" + " Integer,"
                + "content" + " varchar(50)," + "categoryId" + " Integer," + "parentId" + " Integer," + "parentPath" + " varchar(50)," + "time" + " varchar(50)," + "topIndex" + " Integer)";
        db.beginTransaction();
        try {
            db.execSQL(sql);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 创建书签表
     * @param db
     */
    private void createRemarkTable(SQLiteDatabase db) {
        // 该函数是在第一次创建的时候执行，实际上是第一次得到SQLiteDatabase对象的时候才会调用这个方法
        String createSql = "create table " + TABLE_BOOK_MARK + "(" + "id" + " Integer not null primary key autoincrement," + "volumeName" + " varchar(50)," + "volumeId" + " Integer," + "chapterId" + " varchar(50)," + "chapterName" + " varchar(50)," + "chapterPosition" + " Integer,"
                + "chpCount" + " Integer," + "content" + " varchar(500)," + "description" + " varchar(500)," + "type" + " Integer," + "createTime" + " varchar(50)  default '2020-09-01 09:00:00'," + "categoryId" + " varchar(20)," +"categoryName"+ " varchar(100))";
        String createIndexSql = "create index mark_index on "+TABLE_BOOK_MARK+" ('content', 'description')";
        db.beginTransaction();
        try {
            db.execSQL(createSql);
            db.execSQL(createIndexSql);
            db.setTransactionSuccessful();
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            db.endTransaction();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISTORY);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COLLECT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOK_MARK);
        onCreate(db);
    }

    //==================================历史=================================
    public synchronized void addHistory(HistoryBean historyBean) {
        try {
            m_dbWrite.beginTransaction();
            ContentValues values = new ContentValues();
            values.put("chapter", historyBean.getChapter());
            values.put("volumeId", historyBean.getVolumeId());
            values.put("volumeName", historyBean.getVolumeName());
            values.put("chapterCount", historyBean.getChapterCount());
            values.put("indexId", historyBean.getIndexId());
            values.put("content", historyBean.getContent());
            values.put("categoryId", historyBean.getCategoryId());
            values.put("parentId", historyBean.getParentId());
            values.put("parentPath", historyBean.getParentPath());
            values.put("time", TimeUtils.getNowStamp());
            values.put("topIndex", 0);
            m_dbWrite.insert(TABLE_HISTORY, null, values);
            m_dbWrite.setTransactionSuccessful();
            m_dbWrite.endTransaction();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public List<HistoryBean> getAllHistory() {
        List<HistoryBean> historyList = new ArrayList<>();
        try {
            String strSql = "Select * from " + TABLE_HISTORY + " ORDER BY topIndex desc, time DESC";
            Cursor cur = m_dbRead.rawQuery(strSql, null);
            while (cur.moveToNext()) {
                String chapter = cur.getString(cur.getColumnIndex("chapter"));
                int volumeId = cur.getInt(cur.getColumnIndex("volumeId"));
                String volumeName = cur.getString(cur.getColumnIndex("volumeName"));
                int chapterCount = cur.getInt(cur.getColumnIndex("chapterCount"));
                int indexId = cur.getInt(cur.getColumnIndex("indexId"));
                String content = cur.getString(cur.getColumnIndex("content"));
                int categoryId = cur.getInt(cur.getColumnIndex("categoryId"));
                int parentId = cur.getInt(cur.getColumnIndex("parentId"));
                String parentPath = cur.getString(cur.getColumnIndex("parentPath"));
                String time = cur.getString(cur.getColumnIndex("time"));
                int topIndex = cur.getInt(cur.getColumnIndex("topIndex"));
                historyList.add(new HistoryBean(chapter, volumeId, volumeName, chapterCount, indexId, content, categoryId, parentId, parentPath, time, topIndex));
            }
            //		GreenDaoManager.addAllLib(libs);
            cur.close();
            Log.i("xu", "完成");

        } catch (Exception e) {
            e.printStackTrace();
        }
        return historyList;
    }

    /**
     * 获取
     *
     * @return
     */
    public int getMaxTopIndex() {
        int topIndex = 0;
        try {
            String strSql = "Select max(topIndex) from " + TABLE_HISTORY;
            Cursor cur = m_dbRead.rawQuery(strSql, null);
            while (cur.moveToNext()) {
                topIndex = cur.getInt(0);
            }
            //		GreenDaoManager.addAllLib(libs);
            cur.close();
            Log.i("xu", "完成");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return topIndex;
    }

    /**
     * 删除数据库，多个volumeId用,隔开
     *
     * @param volumeIds
     */
    public void deleteHistory(String volumeIds) {
        try {
            m_dbWrite.beginTransaction();
            String sql = "delete from " + TABLE_HISTORY + " where volumeId in (" + volumeIds + ")";
            m_dbWrite.execSQL(sql);
            m_dbWrite.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            m_dbWrite.endTransaction();
        }
    }


    public void clearHistory() {
        try {
            m_dbWrite.beginTransaction();
            String sql = "delete from " + TABLE_HISTORY;
            m_dbWrite.execSQL(sql);
            m_dbWrite.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            m_dbWrite.endTransaction();
        }
    }

    public boolean isExist(int volumeId) {
        Cursor cur = null;
        try {
            String strSql = "Select * from " + TABLE_HISTORY + " where volumeId ="+volumeId;
            cur = m_dbRead.rawQuery(strSql, null);
            if (cur.getCount() != 0)
                return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cur != null)
                cur.close();
        }
        return false;
    }

    public void updateHistory(HistoryBean historyBean) {
        try {
            String sql = "update " + TABLE_HISTORY + " set content = '" + historyBean.getContent() + "' , chapter = '" + historyBean.getChapter() + "' , indexId = '" + historyBean.getIndexId() + "' ,time = '" + TimeUtils.getNowStamp() + "' where volumeId = " + historyBean.getVolumeId();
            m_dbWrite.execSQL(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateHistoryTopInfo(HistoryBean historyBean) {
        try {
            String sql = "update " + TABLE_HISTORY + " set topIndex = '" + historyBean.getTopIndex() + "' where volumeId = '" + historyBean.getVolumeId() + "'";
            m_dbWrite.execSQL(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //======================收藏==============================

    public synchronized void addCollect(CollectBean collectBean) {
        try {
            m_dbWrite.beginTransaction();
            ContentValues values = new ContentValues();
            values.put("chapter", collectBean.getChapter());
            values.put("volumeId", collectBean.getVolumeId());
            values.put("volumeName", collectBean.getVolumeName());
            values.put("chapterCount", collectBean.getChapterCount());
            values.put("indexId", collectBean.getIndexId());
            values.put("content", collectBean.getContent());
            values.put("categoryId", collectBean.getCategoryId());
            values.put("parentId", collectBean.getParentId());
            values.put("parentPath", collectBean.getParentPath());
            values.put("time", TimeUtils.getNowStamp());
            values.put("topIndex", 0);
            m_dbWrite.insert(TABLE_COLLECT, null, values);
            m_dbWrite.setTransactionSuccessful();
            m_dbWrite.endTransaction();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public List<CollectBean> getAllCollectBean() {
        List<CollectBean> historyList = new ArrayList<>();
        try {
            String strSql = "Select * from " + TABLE_COLLECT + " ORDER BY topIndex desc, time DESC";
            Cursor cur = m_dbRead.rawQuery(strSql, null);
            while (cur.moveToNext()) {
                String chapter = cur.getString(cur.getColumnIndex("chapter"));
                int volumeId = cur.getInt(cur.getColumnIndex("volumeId"));
                String volumeName = cur.getString(cur.getColumnIndex("volumeName"));
                int chapterCount = cur.getInt(cur.getColumnIndex("chapterCount"));
                int indexId = cur.getInt(cur.getColumnIndex("indexId"));
                String content = cur.getString(cur.getColumnIndex("content"));
                int categoryId = cur.getInt(cur.getColumnIndex("categoryId"));
                int parentId = cur.getInt(cur.getColumnIndex("parentId"));
                String parentPath = cur.getString(cur.getColumnIndex("parentPath"));
                String time = cur.getString(cur.getColumnIndex("time"));
                int topIndex = cur.getInt(cur.getColumnIndex("topIndex"));
                historyList.add(new CollectBean(chapter, volumeId, volumeName, chapterCount, indexId, content, categoryId, parentId, parentPath, time, topIndex));
            }
            //		GreenDaoManager.addAllLib(libs);
            cur.close();
            Log.i("xu", "完成");

        } catch (Exception e) {
            e.printStackTrace();
        }
        return historyList;
    }

    /**
     * 获取
     *
     * @return
     */
    public int getCollectMaxTopIndex() {
        int topIndex = 0;
        try {
            String strSql = "Select max(topIndex) from " + TABLE_COLLECT;
            Cursor cur = m_dbRead.rawQuery(strSql, null);
            while (cur.moveToNext()) {
                topIndex = cur.getInt(0);
            }
            //		GreenDaoManager.addAllLib(libs);
            cur.close();
            Log.i("xu", "完成");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return topIndex;
    }

    /**
     * 删除数据库，多个volumeId用,隔开
     *
     * @param volumeIds
     */
    public void deleteCollect(String volumeIds) {
        try {
            m_dbWrite.beginTransaction();
            String sql = "delete from " + TABLE_COLLECT + " where volumeId in (" + volumeIds + ")";
            m_dbWrite.execSQL(sql);
            m_dbWrite.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            m_dbWrite.endTransaction();
        }
    }

    public void updateCollect(CollectBean collectBean) {
        try {
            String sql = "update " + TABLE_COLLECT + " set content = '" + collectBean.getContent() + "' , chapter = '" + collectBean.getChapter() + "' , indexId = '" + collectBean.getIndexId() + "' ,time = '" + TimeUtils.getNowStamp() + "' where volumeName = '" + collectBean.getVolumeName() + "'";
            m_dbWrite.execSQL(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void updateCollectTopInfo(CollectBean collectBean) {
        try {
            String sql = "update " + TABLE_COLLECT + " set topIndex = '" + collectBean.getTopIndex() + "' where volumeId = '" + collectBean.getVolumeId() + "'";
            m_dbWrite.execSQL(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public boolean isExistCollect(String volumeName) {
        Cursor cur = null;
        try {
            String strSql = "Select * from " + TABLE_COLLECT + " where volumeName like '" + volumeName + "'";
            cur = m_dbRead.rawQuery(strSql, null);
            if (cur.getCount() != 0)
                return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cur != null)
                cur.close();
        }
        return false;
    }

    //=====================书签===============

    private final String MARK_COLUMId = "id";
    private final String MARK_COLUM_VOLUMEId = "volumeId";
    private final String MARK_COLUM_VOLUMEName = "volumeName";
    private final String MARK_COLUM_CHAPTERId = "chapterId";
    private final String MARK_COLUM_CHAPTERName = "chapterName";
    private final String MARK_COLUM_CHAPTERPosition = "chapterPosition";
    private final String MARK_COLUM_CHPCount = "chpCount";
    private final String MARK_COLUM_CONTENT = "content";
    private final String MARK_COLUM_DESCRIPTION = "description";
    private final String MARK_COLUM_TYPE = "type";
    private final String MARK_COLUM_TIME = "createTime";
    private final String MARK_COLUM_CATEGORY_ID = "categoryId";
    private final String MARK_COLUM_CATEGORY_NAME = "categoryName";


    public ArrayList<Bookmark> getBookmarkList(String sortType, List<String> keyWordList) {
        ArrayList<Bookmark> arrayList = new ArrayList<Bookmark>();
        String[] columns = {MARK_COLUMId, MARK_COLUM_VOLUMEId, MARK_COLUM_VOLUMEName, MARK_COLUM_CHAPTERId, MARK_COLUM_CHAPTERName,
                MARK_COLUM_CHAPTERPosition, MARK_COLUM_CHPCount, MARK_COLUM_CONTENT, MARK_COLUM_DESCRIPTION, MARK_COLUM_TYPE, MARK_COLUM_TIME, MARK_COLUM_CATEGORY_ID, MARK_COLUM_CATEGORY_NAME};
        String[] whereArgs = new String[keyWordList.size() * 2];
        StringBuffer buffer = new StringBuffer();

        for (int i = 0; i < whereArgs.length; i+=2) {
//            { "%" + keyWord + "%", "%" + keyWord + "%"};
            String keyWord = keyWordList.get(i / 2);
            keyWord = sqliteEscape(keyWord);

            whereArgs[i] = "%" + keyWord + "%";
            whereArgs[i+1] = "%" + keyWord + "%";

            //"content like ? or description like ?"
            buffer.append("(content like ? escape '/' or description like ? escape '/' )").append(" and ");
        }
        String whereSql = buffer.toString();
        if (whereSql.endsWith(" and ")){
            whereSql = whereSql.substring(0,whereSql.lastIndexOf(" and ")) ;
        }

        Cursor query = m_dbWrite.query(TABLE_BOOK_MARK, columns, whereSql, whereArgs, null, null, "id " + sortType, "");
        if (query != null) {
            query.moveToFirst();
            while (!query.isAfterLast()) {
                Bookmark bookmark = new BookmarkDataBaseBuilder().build(query);
                arrayList.add(bookmark);
                query.moveToNext();
            }
            query.close();
        }
        return arrayList;
    }

    public void addBookmark(List<Bookmark> bookmarks) {
        // put radio data into the table
        int length = bookmarks.size();
        for (int i = 0; i < length; i++) {
            ContentValues values = new BookmarkDataBaseBuilder().deconstruct(bookmarks.get(i));
            m_dbWrite.insert(TABLE_BOOK_MARK, null, values);
        }
    }

    public void deleteMarkById(String id) {
        String[] whereArgs = {id};
        m_dbWrite.delete(TABLE_BOOK_MARK, "id=?", whereArgs);
    }

    public void deleteMarkByIds(String ids) {
        String sql = "delete from " + TABLE_BOOK_MARK + " where id in (" + ids + ")";
        m_dbWrite.execSQL(sql);
    }


    public static String sqliteEscape(String keyWord){
        keyWord = keyWord.replaceAll("/", "//");
        keyWord = keyWord.replaceAll("'", "''");
        keyWord = keyWord.replaceAll("\\[", "/[");
        keyWord = keyWord.replaceAll("]", "/]");
        keyWord = keyWord.replaceAll("%", "/%");
        keyWord = keyWord.replaceAll("&","/&");
        keyWord = keyWord.replaceAll("_", "/_");
        keyWord = keyWord.replaceAll("\\(", "/(");
        keyWord = keyWord.replaceAll("\\)", "/)");
        return keyWord;
    }
}
